from openpyxl import load_workbook

file_name = 'aqua'


def write_txt(sql: str):
    # sql脚本
    with open('C:/Users/yangjianzhang/Desktop/' + file_name + '.txt', "a", encoding='UTF-8') as f:
        f.write(sql)  # 自带文件关闭功能，不需要再写f.close()
        f.write("\n")


def read_excel():
    path = 'C:/Users/yangjianzhang/Desktop/' + file_name + '.xlsx'
    # 打开文件
    wb = load_workbook(path)
    # 获取所有sheet的名字
    print(wb.sheetnames)
    sheet1 = wb['Sheet1']
    # 获取标题
    titles = sheet1[1]
    print(titles)
    sql = "update mas_org "
    print(titles[1].value)
    # for cell in tuple(titles):
    #     field = cell.value
    #     print(field)

    for index, row in enumerate(sheet1.rows):
        # print(index)
        sql2 = sql
        for i, v in enumerate(row):
            # print(v.value)
            field_val = ''
            if v.value is None:
                field_val = ''
            else:
                field_val = str(v.value)
            if i == 0:
                sql2 = sql2 + " set " + str(titles[i].value) + "='" + field_val + "',"
            else:
                sql2 = sql2 + str(titles[i].value) + "='" + field_val + "',"
        sql2 = sql2.strip(',')
        sql2 = sql2 + "where org_code = '" + str(row[0].value) + "';"
        print(sql2)
        write_txt(sql2)
        # print(row)
        # print(tuple(row))


if __name__ == '__main__':
    read_excel()
